<!---
Copyright: (c) 2006 The MachBlog Authors
Authors: Matt Woodward (mpwoodward@gmail.com) & Peter J. Farrell (pjf@maestropublishing.com)

Licensed under the Apache License, Version 2.0 (the "License");
you may not use this file except in compliance with the License.
You may obtain a copy of the License at

http://www.apache.org/licenses/LICENSE-2.0

Unless required by applicable law or agreed to in writing, software
distributed under the License is distributed on an "AS IS" BASIS,
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
See the License for the specific language governing permissions and
limitations under the License.

$Id$

Notes:
--->
<cfcomponent
	displayname="CategoryDAO"
	output="false"
	extends="CategoryDAO"
	hint="CategoryDAO for MachBlog (PostgreSQL)">

	<!---
	INITIALIZATION / CONFIGURATION
	--->
	<cffunction name="init" access="public" output="false" returntype="CategoryDAO"
		hint="Constructor for this CFC.">
		<cfreturn this />
	</cffunction>

	<!---
	PUBLIC FUNCTIONS - CRUD
	--->
	<cffunction name="create" access="public" output="false" returntype="void" hint="Creates a new category">
		<cfargument name="category" type="Category" required="true" />

		<cfset var checkCategory = "" />
		<cfset var insertCategory = "" />

		<cftry>
			<cfquery name="checkCategory" datasource="#getDatasource().getDsn()#"
					username="#getDatasource().getUserName()#" password="#getDatasource().getPassword()#">
				SELECT category_id
				FROM machblog_category
				WHERE category_name = <cfqueryparam value="#arguments.category.getCategoryName()#" cfsqltype="cf_sql_varchar"
													maxlength="50" />
			</cfquery>
			<cfcatch type="database">
				<cfthrow type="application" message="Database Error" detail="#CFCATCH.Detail#" />
			</cfcatch>
		</cftry>

		<cfif isQuery(checkCategory) AND checkCategory.RecordCount GT 0>
			<cfthrow type="application" message="duplicate category" detail="duplicate category" />
		<cfelse>
			<cftry>
				<cfquery name="insertCategory" datasource="#getDatasource().getDsn()#"
						username="#getDatasource().getUserName()#" password="#getDatasource().getPassword()#">
					INSERT INTO machblog_category (
						category_id,
						category_name,
						created_by_id,
						dt_created,
						ip_created,
						is_active
					) VALUES (
						<cfqueryparam value="#arguments.category.getCategoryId()#" cfsqltype="cf_sql_char"
								maxlength="35" />,
						<cfqueryparam value="#arguments.category.getCategoryName()#" cfsqltype="cf_sql_varchar"
								maxlength="50" />,
						<cfqueryparam value="#arguments.category.getAudit().getCreatedById()#"
								cfsqltype="cf_sql_char" maxlength="35" />,
						<cfqueryparam value="#arguments.category.getAudit().getDtCreated()#" cfsqltype="cf_sql_bigint" />,
						<cfqueryparam value="#arguments.category.getAudit().getIpCreated()#"
								cfsqltype="cf_sql_varchar" maxlength="15" />,
						<cfqueryparam value="#arguments.category.getAudit().getIsActive()#" cfsqltype="cf_sql_smallint" />
					)
				</cfquery>
				<cfcatch type="database">
					<cfthrow type="application" message="Database Error" detail="#CFCATCH.Detail#" />
				</cfcatch>
			</cftry>
		</cfif>
	</cffunction>

	<cffunction name="read" access="public" output="false" returntype="query" hint="Populates a Category bean">
		<cfargument name="categoryId" type="string" required="true" />

		<cfset var getCategory = "" />

		<cfquery name="getCategory" datasource="#getDatasource().getDsn()#"
				username="#getDatasource().getUserName()#" password="#getDatasource().getPassword()#">
			SELECT 	machblog_category.*,
					COUNT(machblog_entry_category.entry_id) AS num_posts,
					cb.first_name AS cbfn,
					cb.last_name AS cbln,
					mb.first_name AS mbfn,
					mb.last_name AS mbln
			FROM 	(machblog_category
						LEFT OUTER JOIN machblog_entry_category
							ON machblog_category.category_id = machblog_entry_category.category_id)
			LEFT OUTER JOIN machblog_entry
				ON machblog_entry_category.entry_id = machblog_entry.entry_id
			LEFT OUTER JOIN machblog_user AS cb
				ON machblog_category.created_by_id = cb.user_id
			LEFT OUTER JOIN machblog_user AS mb
				ON machblog_category.modified_by_id = mb.user_id
			WHERE machblog_category.category_id = <cfqueryparam value="#arguments.categoryId#"
														cfsqltype="cf_sql_char" maxlength="35" />
			GROUP BY 	machblog_category.category_id, machblog_category.category_name,
						machblog_category.created_by_id, machblog_category.dt_created,
						machblog_category.ip_created, machblog_category.modified_by_id,
						machblog_category.dt_modified, machblog_category.ip_modified,
						machblog_category.is_active, cb.first_name, cb.last_name,
						mb.first_name, mb.last_name
			ORDER BY category_name ASC
		</cfquery>

		<cfreturn getCategory />
	</cffunction>

	<cffunction name="update" access="public" output="false" returntype="void"
			hint="Updates a category.">
		<cfargument name="category" type="Category" required="true" />

		<cfset var checkCategory = "" />
		<cfset var updateCategory = "" />

		<cftry>
			<cfquery name="checkCategory" datasource="#getDatasource().getDsn()#"
					username="#getDatasource().getUserName()#" password="#getDatasource().getPassword()#">
				SELECT category_id
				FROM machblog_category
				WHERE category_name = <cfqueryparam value="#arguments.category.getCategoryName()#" cfsqltype="cf_sql_varchar"
													maxlength="50" />
				AND category_id != <cfqueryparam value="#arguments.category.getCategoryId()#" cfsqltype="cf_sql_char" maxlength="35" />
			</cfquery>
			<cfcatch type="database">
				<cfthrow type="application" message="Database Error" detail="#CFCATCH.Detail#" />
			</cfcatch>
		</cftry>

		<cfif isQuery(checkCategory) AND checkCategory.RecordCount GT 0>
			<cfthrow type="application" message="duplicate category" detail="duplicate category" />
		<cfelse>
			<cftry>
				<cfquery name="updateCategory" datasource="#getDatasource().getDsn()#"
						username="#getDatasource().getUserName()#" password="#getDatasource().getPassword()#">
					UPDATE 	machblog_category
					SET 	category_name = <cfqueryparam value="#arguments.category.getCategoryName()#"
															cfsqltype="cf_sql_char" maxlength="50" />,
							modified_by_id = <cfqueryparam value="#arguments.category.getAudit().getModifiedById()#"
															cfsqltype="cf_sql_char" maxlength="35" />,
							dt_modified = <cfqueryparam value="#arguments.category.getAudit().getDtModified()#"
														cfsqltype="cf_sql_bigint" />,
							ip_modified = <cfqueryparam value="#arguments.category.getAudit().getIpModified()#"
														cfsqltype="cf_sql_varchar" maxlength="15" />,
							is_active = <cfqueryparam value="#arguments.category.getAudit().getIsActive()#"
														cfsqltype="cf_sql_smallint" />
					WHERE 	category_id = <cfqueryparam value="#arguments.category.getCategoryId()#"
														cfsqltype="cf_sql_char" maxlength="35" />
				</cfquery>
				<cfcatch type="database">
					<cfthrow type="application" message="Database Error" detail="#CFCATCH.Detail#" />
				</cfcatch>
			</cftry>
		</cfif>
	</cffunction>

	<cffunction name="delete" access="public" output="false" returntype="void"
			hint="Deletes a category.">
		<cfargument name="category" type="Category" required="true" />

		<cfset var deleteEntryCategories = "" />
		<cfset var deleteCategory = "" />

		<cftransaction action="begin">
			<cftry>
				<!--- first delete all the existing entry_category records with this category --->
				<cfquery name="deleteEntryCategories" datasource="#getDatasource().getDsn()#"
						username="#getDatasource().getUserName()#"
						password="#getDatasource().getPassword()#">
					DELETE FROM machblog_entry_category
					WHERE category_id = <cfqueryparam value="#arguments.category.getCategoryId()#"
														cfsqltype="cf_sql_char" maxlength="35" />
				</cfquery>

				<!--- now delete the category --->
				<cfquery name="deleteCategory" datasource="#getDatasource().getDsn()#"
						username="#getDatasource().getUserName()#"
						password="#getDatasource().getPassword()#">
					DELETE FROM machblog_category
					WHERE category_id = <cfqueryparam value="#arguments.category.getCategoryId()#"
														cfsqltype="cf_sql_char" maxlength="35" />
				</cfquery>
				<cftransaction action="commit" />

				<cfcatch type="database">
					<cftransaction action="rollback" />
					<cfthrow type="application" message="Database Error" detail="#CFCATCH.Detail#" />
				</cfcatch>
			</cftry>
		</cftransaction>
	</cffunction>
</cfcomponent>